/*
 *  Copyright 2019-2020 Zheng Jie
 *
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *  http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 */
package me.zhengjie.modules.biz.repository;

import me.zhengjie.modules.biz.domain.StorehouseOrderDetail;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.util.ArrayList;
import java.util.List;

/**
 * @author Admin
 * @website https://eladmin.vip
 * @date 2023-03-13
 **/
public interface StorehouseOrderDetailRepository extends JpaRepository<StorehouseOrderDetail, Integer>, JpaSpecificationExecutor<StorehouseOrderDetail> {
    @Query(value = "select * from hj_storehouse_order_detail where order_id = ?1 ", nativeQuery = true)
    List<StorehouseOrderDetail> findAllByOrderId(Integer orderId);

    @Query(value = "select distinct name from hj_storehouse_order_detail where order_id = ?1", nativeQuery = true)
    List<String> findNameByOrderId(Integer orderId);

    @Query(value = "SELECT * FROM hj_storehouse_order_detail WHERE name=?1 and number = ?2 AND hj_hazardous_chemical_id IS NULL limit 0,1", nativeQuery = true)
    StorehouseOrderDetail findByNameAmount(String name, Float amount);

    @Query(value = "SELECT * FROM hj_storehouse_order_detail WHERE name=?1 AND hj_hazardous_chemical_id IS NULL limit 0,1", nativeQuery = true)
    StorehouseOrderDetail findByName(String name);

    @Query(value = "SELECT * FROM hj_storehouse_order_detail WHERE name=?1 AND order_id = ?2 AND hj_hazardous_chemical_id IS NULL limit 0,1", nativeQuery = true)
    StorehouseOrderDetail getByNameAndOrderIdToBeApply(String name, Integer orderId);

    @Query(value = "SELECT * FROM hj_storehouse_order_detail WHERE name=?1 AND order_id = ?2 AND hj_hazardous_chemical_id IS NOT NULL limit 0,1", nativeQuery = true)
    StorehouseOrderDetail getByNameAndOrderIdApplied(String name, Integer orderId);

    @Query(value = "SELECT * FROM hj_storehouse_order_detail WHERE order_id = ?1 and cabinet_id = ?2 GROUP BY name", nativeQuery = true)
    List<StorehouseOrderDetail> findBySid(Integer id, Integer cabinetId);

    @Query(value = "SELECT * FROM hj_storehouse_order_detail WHERE name=?1 AND order_id = ?2 limit 0,1", nativeQuery = true)
    StorehouseOrderDetail findByNameAndOrderId(String name, Integer id);

    @Query(value = "SELECT hj_hazardous_chemical_id FROM hj_storehouse_order_detail WHERE hj_hazardous_chemical_id IS NOT NULL", nativeQuery = true)
    List<Integer> findHcId();

    @Query(value = "SELECT * FROM hj_storehouse_order_detail WHERE hj_hazardous_chemical_id = ?1 and status = 5 limit 0,1", nativeQuery = true)
    StorehouseOrderDetail findByHcIdToBeReturned(Integer hcId);

    @Query(value = "SELECT * FROM hj_storehouse_order_detail WHERE order_id = ?1", nativeQuery = true)
    ArrayList<StorehouseOrderDetail> findByOrderId(Integer id);

    @Query(value = "DELETE FROM hj_storehouse_order_detail WHERE order_id = ?1", nativeQuery = true)
    @Modifying
    int deleteByOrderId(Integer orderId);

    @Query(value = "SELECT * FROM hj_storehouse_order_detail sd ,hj_storehouse_order so \n" +
            "WHERE  ((sd.type = 1 AND sd.category in ('3','4') ) or ((sd.type = 2 and sd.unit != '1' and sd.unit != '支' ) or sd.type = 3)) AND sd.status in (5,6)  AND sd.order_id = so.id AND so.user_id = ?1 \n" +
            "GROUP BY sd.name ,sd.status order by sd.created_at desc limit ?2,?3", nativeQuery = true)
    List<StorehouseOrderDetail> findOrderDetail(Long userId, int page, int size);

    @Query(value = "SELECT count(*) as number FROM hj_storehouse_order_detail sd ,hj_storehouse_order so \n" +
            "WHERE  ((sd.type = 1 AND sd.category in ('3','4') ) or ((sd.type = 2 and sd.unit != '1' and sd.unit != '支' ) or sd.type = 3)) AND sd.status in (5,6)  AND sd.order_id = so.id AND so.user_id = ?1 \n" +
            "GROUP BY sd.name ,sd.status order by sd.created_at desc ", nativeQuery = true)
    StorehouseOrderDetail countOrderDetail(Long userId);

    @Query(value = "SELECT * FROM hj_storehouse_order_detail sd ,hj_storehouse_order so \n" +
            "WHERE ((sd.type = 1 AND sd.category in ('3','4') ) or ((sd.type = 2 and sd.unit != '1' and sd.unit != '支' ) or sd.type = 3)) AND sd.status in (5,6) AND sd.name like ?1 AND sd.order_id = so.id AND so.user_id = ?2 \n" +
            "GROUP BY sd.name,sd.status order by sd.created_at desc limit ?3,?4", nativeQuery = true)
    List<StorehouseOrderDetail> findOrderDetailByName(String name, Long userId, int page, int size);

    @Query(value = "SELECT count(*) FROM hj_storehouse_order_detail sd ,hj_storehouse_order so \n" +
            "WHERE ((sd.type = 1 AND sd.category in ('3','4') ) or ((sd.type = 2 and sd.unit != '1' and sd.unit != '支' ) or sd.type = 3)) AND sd.status in (5,6) AND sd.name like ?1 AND sd.order_id = so.id AND so.user_id = ?2 \n" +
            "GROUP BY sd.name,sd.status order by sd.created_at desc", nativeQuery = true)
    int countOrderDetailByName(String name, Long userId);

    @Query(value = "SELECT * FROM hj_storehouse_order_detail sd ,hj_storehouse_order so WHERE ((sd.type = 1 AND sd.category in ('3','4') ) or ((sd.type = 2 and sd.unit != '1' and sd.unit != '支' ) or sd.type = 3)) AND sd.status = ?1 AND sd.order_id = so.id AND so.user_id = ?2 GROUP BY sd.name order by sd.created_at desc  limit ?3,?4", nativeQuery = true)
    List<StorehouseOrderDetail> findOrderDetailByStatus(String status, Long userId, int page, int size);

    @Query(value = "SELECT count(*) FROM hj_storehouse_order_detail sd ,hj_storehouse_order so WHERE ((sd.type = 1 AND sd.category in ('3','4') ) or ((sd.type = 2 and sd.unit != '1' and sd.unit != '支' ) or sd.type = 3)) AND sd.status = ?1 AND sd.order_id = so.id AND so.user_id = ?2 GROUP BY sd.name order by sd.created_at desc", nativeQuery = true)
    int countOrderDetailByStatus(String status, Long userId);

    @Query(value = "SELECT * FROM hj_storehouse_order_detail sd ,hj_storehouse_order so WHERE ((sd.type = 1 AND sd.category in ('3','4') ) or ((sd.type = 2 and sd.unit != '1' and sd.unit != '支' ) or sd.type = 3)) AND sd.status = ?2 AND sd.name like ?1 AND sd.order_id = so.id AND so.user_id = ?3 GROUP BY sd.name order by sd.created_at desc limit ?4,?5", nativeQuery = true)
    List<StorehouseOrderDetail> findOrderDetailByNameAndStatus(String name, String status, Long userId, int page, int size);

    @Query(value = "SELECT count(*) FROM hj_storehouse_order_detail sd ,hj_storehouse_order so WHERE ((sd.type = 1 AND sd.category in ('3','4') ) or ((sd.type = 2 and sd.unit != '1' and sd.unit != '支' ) or sd.type = 3)) AND sd.status = ?2 AND sd.name like ?1 AND sd.order_id = so.id AND so.user_id = ?3 GROUP BY sd.name order by sd.created_at desc", nativeQuery = true)
    int countOrderDetailByNameAndStatus(String name, String status, Long userId);

    @Query(value = "SELECT SUM(count_type) FROM hj_storehouse_order_detail sd ,hj_storehouse_order so WHERE sd.name = ?1 AND sd.category = ?2 AND sd.type = ?3 AND sd.status = ?4 AND sd.order_id = so.id AND so.user_id = ?5", nativeQuery = true)
    Integer sum(String name, String category, int type, int status, Long userId);

    @Query(value = "SELECT SUM(count_type) FROM hj_storehouse_order_detail sd ,hj_storehouse_order so WHERE sd.name = ?1 AND sd.type = ?2 AND sd.status = ?3 AND sd.order_id = so.id AND so.user_id = ?4", nativeQuery = true)
    Integer sum(String name, int type, int status, Long userId);

    @Query(value = "SELECT * FROM hj_storehouse_order_detail sd WHERE ((sd.type = 1 AND sd.category in ('3','4') ) or ((sd.type = 2 and sd.unit != '1' and sd.unit != '支') or sd.type = 3)) AND sd.status = ?1 AND sd.cabinet_id in ?2 order by sd.id desc", nativeQuery = true)
    List<StorehouseOrderDetail> findOrderDetailByStatusCabinetIdList(int status, int[] cabinetIdList);

    @Query(value = "SELECT distinct order_id FROM hj_storehouse_order_detail WHERE status = 2 AND cabinet_id in ?1", nativeQuery = true)
    List<Integer> findToBeDeliveredOrderIdByCabinetIds(int[] cabinetIdList);
}
